{% load i18n %}
{
    "personnel.Employee":{
            "params":"",  
            "verbose_fields":{
                "4":"Gender"
            },
            "head":["{% trans '人员编号' %}","{% trans '姓名' %}","{% trans '部门编号' %}","{% trans '部门名称' %}","{% trans '性别' %}","{% trans '手机' %}","{% trans '生日' %}","{% trans '卡号' %}","{% trans '指纹模板' %}","{% trans '考勤区域' %}"],
            "db_fields":["badgenumber","name","code","DeptName","gender", "pager", "Birthday","Card","TEMP","SUM_AREA"],
            "sql": "SELECT badgenumber,name,code,DeptName,gender, pager, Birthday,Card,TEMP,SUM_AREA
                    FROM(
                        SELECT ROW_NUMBER() OVER ( ORDER BY u.badgenumber ASC) as row_num,  
                                u.badgenumber, u.name,d.code,d.DeptName,u.gender,pager,
                                u.Birthday,Card,
                        (
                            (SELECT  '10.0:'+CONVERT(varchar,COUNT(templateid)) COUNT_T FROM template t WHERE t.userid=u.userid and t.Fpversion=10)
                            +','
                            +(SELECT  '9.0:'+CONVERT(varchar,COUNT(templateid)) COUNT_T FROM template t WHERE t.userid=u.userid and t.Fpversion=9)
                        )as TEMP,
                        (SELECT areaname 
                            FROM ( 
                                SELECT 
                                   areaname + ','
                                FROM (
                                    SELECT DISTINCT areaname 
                                    FROM personnel_area a,userinfo_attarea ua
                                    where ua.area_id=a.id and ua.employee_id=u.userid and not(a.status=3) and not(a.status=999)
                                ) AS SUM_COL 
                                FOR XML PATH('') 
                            )as SUM_AREA(areaname)
                        ) as SUM_AREA
                        from userinfo u, departments d
                        where u.defaultdeptid= d.DeptId and not(u.status=3) and not(u.status=999) and %s
                    ) AS RECORDS WHERE row_num>=%s and row_num<=%s 
                    "
    },
        "iclock.Transaction":{
                   "params":"",  
                   "verbose_fields":{
                       "3":"State"
                   },
                   "head":["{% trans '员工编号' %}","{% trans '姓名' %}","{% trans '考勤时间' %}","{% trans '考勤状态' %}","{% trans '设备序列号' %}"],
                   "db_fields":["badgenumber","name", "checktime", "checktype","sn_name"],
                   "sql": " 
                        SELECT badgenumber,name,checktime,checktype,sn_name
                        FROM (
                            select ROW_NUMBER() OVER ( ORDER BY  checktime DESC, badgenumber ASC) as row_num, userinfo.badgenumber as badgenumber,userinfo.name as name,checktime,checktype,sn_name
                                from checkinout,userinfo 
                                where userinfo.userid=checkinout.userid and %s
                        )
                         AS RECORDS WHERE row_num>=%s and row_num<=%s 
                           "
           },
        "iaccess.AccRTMonitor":{
                    "params":"",
                    "verbose_fields":{
                    	"4":"event_type",
                        "10":"verified"
                    },
                    "head":["{% trans '时间' %}","{% trans '区域' %}","{% trans '设备' %}","{% trans '事件点' %}","{% trans '事件描述' %}","{% trans '卡号' %}","{% trans '人员编号' %}","{% trans '姓 名' %}","{% trans '部门' %}","{% trans '状态' %}","{% trans '验证方式' %}","{% trans '备注' %}"],
                    "db_fields":["time","area","device_name","event_point_name","event_type","card_no","pin","name","dept","state_name","verified","description"],
                    "sql":"
                        SELECT time,area,device_name,event_point_name,event_type,card_no,pin,name,dept,state_name,verified,description 
                        FROM (
                            select ROW_NUMBER() OVER ( ORDER BY time DESC) as row_num,
                            a.time,a.area,a.device_name,a.event_point_name,a.event_type,a.card_no,a.pin,(a.firstname+' '+a.lastname) as name,a.dept,a.state_name,a.verified,a.description 
                            from acc_monitor_log a left join userinfo u on a.pin = u.badgenumber where a.%s 
                        )
                        AS RECORDS WHERE row_num >= %s and row_num <= %s  
                    "
        }
        
}
